import pandas as pd
import numpy as np

def equal_split(total, n):
    base = round(total / n, 2)
    amounts = [base] * n
    residual = round(total - sum(amounts), 2)
    if abs(residual) > 0.01:
        amounts[0] = round(amounts[0] + residual, 2)
    return amounts

def ensure_sum_fix(amounts, target_total):
    current_sum = round(sum(amounts), 2)
    diff = round(target_total - current_sum, 2)
    if abs(diff) > 0.01:
        amounts[-1] = round(amounts[-1] + diff, 2)
    return amounts

INPUT_PATH = "runs\\20251004-080326\\input.xlsx"
df = pd.read_excel(INPUT_PATH, sheet_name='退货明细', engine='openpyxl')

person_stats = df.groupby('Person').agg(
    Items=('Item', 'count'),
    TotalAmount=('Amount', 'sum')
).reset_index()

total_order_amount = df['Amount'].sum()
num_people = len(person_stats)

share_amounts = equal_split(total_order_amount, num_people)
share_amounts = ensure_sum_fix(share_amounts, total_order_amount)

person_stats['ShareAmount'] = share_amounts
person_stats['ShareAmount'] = person_stats['ShareAmount'].round(2)

result_df = person_stats[['Person', 'Items', 'ShareAmount']]

with pd.ExcelWriter(INPUT_PATH, mode='a', engine='openpyxl', if_sheet_exists='replace') as writer:
    result_df.to_excel(writer, sheet_name='Result', index=False)

print("DONE: 退货统计与金额分摊完成")